package com.cpi.cornpp.purchaseorder.spot.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.purchaseorder.spot.dto.PurchaseOrderSpotDTO;
import com.cpi.cornpp.purchaseorder.spot.exception.PurchaseOrderSpotException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class PurchaseOrderSpotDAO extends DBDAO{
 private static final String SELECT_ORDER_PURCHASE_SPOT = "select purchase_order_spot_id,purchase_order_id,quantity,purchase_date,unit_price,(select qty from units where unit_id = 'BUS') from purchases_orders_spots where purchase_order_id = ?";
 private static final String INSERT_ORDER_PURCHASE_SPOT = "insert into purchases_orders_spots (purchase_order_id,quantity,purchase_date,unit_price) values (?,?,?,?)";
 private static final String UPDATE_ORDER_PURCHASE_SPOT = "update purchases_orders_spots set quantity = ?, purchase_date = ?, unit_price = ? where purchase_order_spot_id = ?";
 private static final String DELETE_ORDER_PURCHASE_SPOT = "delete from purchases_orders_spots where purchase_order_spot_id = ?";
 /**
  * @author Edgar Joao
  * @param purchaseOrderId
  * @return
  * @throws PurchaseOrderSpotException
  */
 public List getPurchaseOrderSpot(int purchaseOrderId) throws PurchaseOrderSpotException{
		List list = null;
		PreparedStatement preparedStatement = null;
		PurchaseOrderSpotDTO purchaseOrderSpotDTO = null;
		ResultSet rs = null;
		try{				
			list = new ArrayList(0);			
			preparedStatement = prepareSQL(SELECT_ORDER_PURCHASE_SPOT, dsName);
			preparedStatement.setInt(1, purchaseOrderId);
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				purchaseOrderSpotDTO = new PurchaseOrderSpotDTO();
				purchaseOrderSpotDTO.setPurchaseOrderSpotId(rs.getInt(1));
				purchaseOrderSpotDTO.setPurchaseOrderId(rs.getInt(2));
				purchaseOrderSpotDTO.setQuantity(rs.getDouble(3));
				purchaseOrderSpotDTO.setPurchaseDate(rs.getDate(4));
				purchaseOrderSpotDTO.setUnitPrice(rs.getDouble(5));
				purchaseOrderSpotDTO.setBushelUnit(rs.getDouble(6));
				list.add(purchaseOrderSpotDTO);
			}	
		}catch (PoolException poolException){
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			poolException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw purchaseOrderSpotException;	
		} catch (SQLException sqlException) {
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			sqlException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw purchaseOrderSpotException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return list;		
	}
 /**
  * @author Edgar Joao
  * @param dto
  * @throws PurchaseOrderSpotException
  */
 public void insertPurchaseOrderSpot(PurchaseOrderSpotDTO dto) throws PurchaseOrderSpotException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(INSERT_ORDER_PURCHASE_SPOT, dsName);			
			preparedStatement.setInt(1, dto.getPurchaseOrderId());
			preparedStatement.setDouble(2, dto.getQuantity());									
			preparedStatement.setDate(3, dto.getPurchaseDate());
			preparedStatement.setDouble(4, dto.getUnitPrice());
			
			preparedStatement.executeUpdate();
		}catch (PoolException poolException){
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			poolException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw purchaseOrderSpotException;	
		} catch (SQLException sqlException) {
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			sqlException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw purchaseOrderSpotException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
 /**
  * @author Edgar Joao
  * @param dto
  * @throws PurchaseOrderSpotException
  */
 public void updatePurchaseOrderSpot(PurchaseOrderSpotDTO dto) throws PurchaseOrderSpotException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(UPDATE_ORDER_PURCHASE_SPOT, dsName);			
			preparedStatement.setDouble(1, dto.getQuantity());
			preparedStatement.setDate(2, dto.getPurchaseDate());
			preparedStatement.setDouble(3, dto.getUnitPrice());
			preparedStatement.setInt(4, dto.getPurchaseOrderSpotId());
			preparedStatement.executeUpdate();
		}catch (PoolException poolException){
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			poolException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw purchaseOrderSpotException;	
		} catch (SQLException sqlException) {
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			sqlException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw purchaseOrderSpotException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
  
 	public void deletePurchaseOrderSpot(int purchaseOrderSpotId) throws PurchaseOrderSpotException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(DELETE_ORDER_PURCHASE_SPOT, dsName);			
			preparedStatement.setDouble(1, purchaseOrderSpotId);
			preparedStatement.executeUpdate();
		}catch (PoolException poolException){
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			poolException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw purchaseOrderSpotException;	
		} catch (SQLException sqlException) {
			PurchaseOrderSpotException purchaseOrderSpotException = null;
			sqlException.printStackTrace(System.out);
			purchaseOrderSpotException = new PurchaseOrderSpotException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw purchaseOrderSpotException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}
